package reports.bestsellers;

import reports.ReportUtilities;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.IOException;
import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.util.Map;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Created by IntelliJ IDEA.
 * User: raj.chhabra
 * Date: Jan 13, 2011
 * Time: 5:16:41 PM
 * To change this template use File | Settings | File Templates.
 */
public class BestSellerReport extends ReportUtilities {

    private static final String[] titles = {"Vendor", "VSN","Description","Total Sales"};
    private static final String[] values = {"ve_cd","vsn","des","totalSales"};
    private static final String reportTitle = "2010 Best Seller Report";

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        BestSellerReport report = new BestSellerReport();
        Connection conn = report.getDBConnection(args[0]);
        report.fileName = args[1];

        //Gets Best Seller List ordered by total sales desc
        String bestSellerQuery = "select t1.*, itm.ve_cd,itm.vsn,itm.des from itm," +
                //"(select so_ln.itm_cd, TO_CHAR(sum(so_ln.qty*so_ln.unit_prc),'$99999.99') as totalsales from so, so_ln " +
                "(select so_ln.itm_cd, sum(so_ln.qty*so_ln.unit_prc) as totalsales from so, so_ln " +
                "where so.stat_cd in ('F') " +
                "and SO.ORD_TP_CD = 'SAL' " +
                "and SO.STAT_CD <> 'V' " +
                "and so_wr_dt between ('01-Jan-2010') and ('31-DEC-2010') " +
                "and SO_LN.VOID_FLAG='N' " +
                "and so.del_doc_num=so_ln.del_doc_num " +
                "group by so_ln.itm_cd) t1 " +
                "where t1.itm_cd = itm.itm_cd " +
                "order by t1.totalsales desc";

        Statement stmt = conn.createStatement();
        System.out.println("calling executQuery()");
        ResultSet rs = stmt.executeQuery(bestSellerQuery);
        try {
            report.createSpreadsheet(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            stmt.close();
        }        
    }


    public void createSpreadsheet(ResultSet rs) throws SQLException, IOException {
        Workbook wb = new HSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = createNewSheet((HSSFWorkbook)wb, "Best Seller Report", titles, reportTitle, styles);

        Row row;
        Cell cell;
        int rownum = 2;


        while(rs.next()){
            int j=0;
            row = sheet.createRow(rownum);

            for (String value:values) {
                cell = row.createCell(j);
                cell.setCellValue(rs.getString(value));
                if (value.equals("ve_cd"))
                    cell.setCellStyle(styles.get("cell_normal_center"));
                else                                    
                    cell.setCellStyle(styles.get("cell_normal"));

                j++;
            }
            rownum++;
        }

        Sheet sheet1 = wb.getSheetAt(0);

        for (int k=0; k<values.length; k++){
            sheet1.autoSizeColumn((short)k);
        }

        // Write the output to a file
        //String file = "CustomerReportCAN.xls";
        String file = fileName;
        if(wb instanceof XSSFWorkbook) file += "x";
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();

    }

    public void createSpreadsheet() throws SQLException, IOException {
        //To change body of implemented methods use File | Settings | File Templates.
    }
}
